package org.itec.yeast.trdo;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.itec.yeast.db.DataManager;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * Yeast基因表操作类，通过调用类DataManager中的方法对表yeast.gene进行操作。
 * @author GGCoke
 *
 */
public class TRYeastGeneSQL {
	private static final Logger log = LoggerFactory.getLogger(TRYeastGeneSQL.class);
	private static final String SQL_INSERT = "insert into gene("
			+ "Primary_SGDID, Feature_Type, Feature_Qualifier, Feature_Name, "
			+ "Standard_Gene_Name, Alias, Parent_Feature_Name, Secondary_SGDID, "
			+ "Chromosome, Start_Coordinate, Stop_Coordinate, Strand, "
			+ "Genetic_Position, Coordinate_Version, Sequence_Version, Description"
			+ ") values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ";
	
	
	private Object[] getParams(TRYeastGene yeast){
		Object[] params = new Object[]{
				yeast.getPrimarySGDID(), 
				yeast.getFeatureType(),
				yeast.getFeatureQualifier(),
				yeast.getFeaturreName(),
				yeast.getStandardName(),
				yeast.getAlias(),
				yeast.getParentFeatureName(),
				yeast.getSecondSGDID(),
				yeast.getChromosome(),
				yeast.getStartCoordinate(),
				yeast.getStopCoordinate(),
				yeast.getStrand(),
				yeast.getGeneticPosition(),
				yeast.getCoordinateVersion(),
				yeast.getSequenceVersion(),
				yeast.getDescciption()
			};
		
		return params;
	}
	
	public List<Map<String, String>> getSGD_IDs(){
		String sql = "SELECT Primary_SGDID FROM gene";
		List<Map<String, String>> SGD_IDs = DataManager.executeQuery(sql);
		return SGD_IDs;
	}
	
	public Map<String, Integer> getGeneIDs(){
		Map<String, Integer> geneIDs = new HashMap<String, Integer>();
		String sql = "select idGENE, Feature_Name from gene";
		List list = DataManager.executeQuery(sql);
		if (list == null || list.size() == 0){
			log.error("表GENE中没有数据。请先获取Gene信息");
		} else {
			for (int i = 0; i < list.size(); i++){
				Map map = (Map) list.get(i);
				geneIDs.put((String)map.get("Feature_Name"), (Integer)map.get("idGENE"));
			}
		}
		return geneIDs;
	}
	
	/**
	 * 插入单条记录
	 * @param yeast
	 */
	public void insert(TRYeastGene yeast){
		Object[] params = getParams(yeast);
		int result = DataManager.executeUpdate(SQL_INSERT, params);
		if (result != 0){
			log.info("插入YeastGene数据成功");
		} else {
			log.info("未插入YeastGene数据");
		}
	}
	
	/**
	 * 插入多条记录
	 * @param yeasts
	 */
	public void insert(List<TRYeastGene> yeasts){
		int result = -1;
		List<Object[]> list = new ArrayList<Object[]>();
		long start = System.currentTimeMillis();
		for (int i = 0; i < yeasts.size(); i++){
			TRYeastGene yeast = yeasts.get(i);
			Object[] params = getParams(yeast);
			list.add(params);
		}
		
		result = DataManager.executeBatchInsert(SQL_INSERT, list);
		long end = System.currentTimeMillis();
		log.info("插入" + result + "条YeastGene记录，共用" + (end - start) + "ms");
	}
}
